﻿CREATE TABLE DBDictionaryService.DBInstrumentStaticData(
	ID										uniqueidentifier PRIMARY KEY,
	DBMarketID								uniqueidentifier NOT NULL,
	Type									varchar(32) NOT NULL,
	Name									varchar(64) NOT NULL,
	Code									varchar(64) NOT NULL,
	ISIN									varchar(64) NOT NULL,
	TradingScope							varchar(32) NOT NULL,				/* See ETradingScope */
    Currency								varchar(3) NOT NULL,
	DBTickSizeRuleID						uniqueidentifier NOT NULL,
    DBFeedPluginID							uniqueidentifier,    				/* Uses this layer for feed */
	DBFeedPluginInstrumentStaticDataConfigurationID	uniqueidentifier,			/* Configuration needed by this layer for this instrument */

    DBOrderPluginID							uniqueidentifier,    				/* Uses this layer for orders */
	DBOrderPluginInstrumentStaticDataConfigurationID	uniqueidentifier,		/* Configuration needed by this layer for this instrument */

	PointValue								float,								/* Can be null */
	LotSize									int,								/* Can be null */
	    
    FOREIGN KEY (DBMarketID)				REFERENCES DBDictionaryService.DBMarket(ID) ON DELETE CASCADE,
    Unique( DBMarketID, Code, TradingScope ),									/* Inside a given Market cannot have same code twice for the same trading scope */
    CHECK( 
		TradingScope in (
			'Production',
			'Virtual',
			'Test')
	),
					    
    CHECK( 
		Type in (
			'Equity',
			'EquityIndexFuture',
			'EquityVanillaOption',
			'Forex',
			'ForexFuture') 
	),
    
    /* InstrumentStaticData Security */
	Enabled									bit NOT NULL default(1),
	BuyEnabled								bit NOT NULL default(1),
	SellEnabled								bit NOT NULL default(1),
	ShortSellEnabled						bit NOT NULL default(1),
	BlackListed								bit NOT NULL default(0),
	HardToBorrow							bit NOT NULL default(0),
	Restricted								bit NOT NULL default(0),
	
	/* Insrument Risk Limitation */
	MaxNominalTrade							float NOT NULL default(0.0), CHECK(MaxNominalTrade>=0.0),
	MaxNominalBook							float NOT NULL default(0.0), CHECK(MaxNominalBook>=0.0),
	MaxVariationPercentage					float NOT NULL default(0.2), CHECK(MaxVariationPercentage>=0.0 and MaxVariationPercentage<=1.0),
	
    /* Aggregable Fields, see views. Be warned to keep the same field names in both view and table, else the auto CopyProperties C# method won't work */

    Currency2					varchar(3),
    Sector						varchar(32), /* See: ESector */ 
    Maturity					datetime2,
    Tenor						datetime2,
    Strike						float,
    K							float,
    K1							float,
    K2							float,
    T							datetime2,
    T1							datetime2,
    T2							datetime2,
    DBUnderlyingID				uniqueidentifier, FOREIGN KEY(DBUnderlyingID)	REFERENCES DBDictionaryService.DBInstrumentStaticData(ID),
    DBUnderlying1ID				uniqueidentifier, FOREIGN KEY(DBUnderlying1ID)	REFERENCES DBDictionaryService.DBInstrumentStaticData(ID),
    DBUnderlying2ID				uniqueidentifier, FOREIGN KEY(DBUnderlying2ID)	REFERENCES DBDictionaryService.DBInstrumentStaticData(ID),
	OptionType					varchar(32), /* See EOptionType */ 
	BarrierType					varchar(32), /* See EBarrierType */ 
	ExerciseType				varchar(32), /* See EExerciseType */ 
	
	_InsertionStamp							bigint,
	_UpdateStamp							bigint,
);
